/* ---------------------------------------------------------------------- */
/* Script generated with: DeZign for Databases v4.2.0                     */
/* Target DBMS:           Oracle 10g                                      */
/* Project file:          ocumed_v10.dez                                  */
/* Project name:          Ocumed                                          */
/* Author:                Daniel Rudigier, Andreas Willburger             */
/* Script type:           Database creation script                        */
/* Created on:            2008-06-23 16:17                                */
/* ---------------------------------------------------------------------- */


/* ---------------------------------------------------------------------- */
/* Sequences                                                              */
/* ---------------------------------------------------------------------- */

CREATE SEQUENCE seq_patient
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    NOMAXVALUE
    nocycle
    noorder;

CREATE SEQUENCE seq_ort
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    NOMAXVALUE
    nocycle
    noorder;

CREATE SEQUENCE seq_vertretung
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    NOMAXVALUE
    nocycle
    noorder;

CREATE SEQUENCE seq_termin
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    NOMAXVALUE
    nocycle
    noorder;

CREATE SEQUENCE seq_hauptvers
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    NOMAXVALUE
    nocycle
    noorder;

CREATE SEQUENCE seq_terminart
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    NOMAXVALUE
    nocycle
    noorder;

CREATE SEQUENCE seq_zeitbestaetigung
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    NOMAXVALUE
    nocycle
    noorder;

CREATE SEQUENCE seq_anamnese
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    NOMAXVALUE
    nocycle
    noorder;

CREATE SEQUENCE seq_rechnung
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    NOMAXVALUE
    nocycle
    noorder;

CREATE SEQUENCE seq_warteposition
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    NOMAXVALUE
    nocycle
    noorder;

CREATE SEQUENCE seq_benutzer
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    NOMAXVALUE
    nocycle
    noorder;

CREATE SEQUENCE seq_ordination
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    NOMAXVALUE
    nocycle
    noorder;

CREATE SEQUENCE seq_rezept
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    NOMAXVALUE
    nocycle
    noorder;

CREATE SEQUENCE seq_fehlzeit
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    NOMAXVALUE
    nocycle
    noorder;

CREATE SEQUENCE seq_gruppe
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    NOMAXVALUE
    nocycle
    noorder;

CREATE SEQUENCE seq_recht
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    NOMAXVALUE
    nocycle
    noorder;

CREATE SEQUENCE seq_medikament
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    NOMAXVALUE
    nocycle
    noorder;

CREATE SEQUENCE seq_leistung
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    NOMAXVALUE
    nocycle
    noorder;

CREATE SEQUENCE seq_mediver
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    NOMAXVALUE
    nocycle
    noorder;

CREATE SEQUENCE seq_brillenver
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    NOMAXVALUE
    nocycle
    noorder;

CREATE SEQUENCE seq_ueberweisung
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    NOMAXVALUE
    nocycle
    noorder;

CREATE SEQUENCE seq_behandlung
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    NOMAXVALUE
    nocycle
    noorder;

CREATE SEQUENCE seq_position
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    NOMAXVALUE
    nocycle
    noorder;

CREATE SEQUENCE seq_land
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    NOMAXVALUE
    nocycle
    noorder;

CREATE SEQUENCE seq_versicherung
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    NOMAXVALUE
    nocycle
    noorder;

/* ---------------------------------------------------------------------- */
/* Tables                                                                 */
/* ---------------------------------------------------------------------- */

/* ---------------------------------------------------------------------- */
/* Add table "oc_patient"                                                 */
/* ---------------------------------------------------------------------- */

CREATE TABLE oc_patient (
    patientID NUMBER(8) NOT NULL,
    ortID NUMBER(8),
    arztID NUMBER(8) NOT NULL,
    geandertVonID NUMBER(8) NOT NULL,
    geandertAm DATE NOT NULL,
    patientVorname VARCHAR2(255) DEFAULT '0' NOT NULL,
    patientNachname VARCHAR2(255) DEFAULT '0' NOT NULL,
    patientGeschlecht CHAR(1) DEFAULT '0' NOT NULL,
    patientAnrede VARCHAR2(1000),
    patientSVN VARCHAR2(40) DEFAULT '0',
    patientGebDatum DATE NOT NULL,
    patientTel VARCHAR2(30),
    patientFax VARCHAR2(30),
    patientEmail VARCHAR2(1000),
    patientBesitztEcard CHAR(1) DEFAULT '0',
    patientAdresse VARCHAR2(1000) DEFAULT '0',
    patientStatus CHAR(1) DEFAULT '0' NOT NULL,
    CONSTRAINT PK_oc_patient PRIMARY KEY (patientID)
);

CREATE INDEX IDX_oc_patient_1 ON oc_patient (ortID);

CREATE INDEX IDX_oc_patient_2 ON oc_patient (arztID);

/* ---------------------------------------------------------------------- */
/* Add table "oc_ort"                                                     */
/* ---------------------------------------------------------------------- */

CREATE TABLE oc_ort (
    ortID NUMBER(8) NOT NULL,
    landID NUMBER(8) DEFAULT 0 NOT NULL,
    ortPLZ VARCHAR2(10) NOT NULL,
    ortName VARCHAR2(255) DEFAULT '0' NOT NULL,
    CONSTRAINT PK_oc_ort PRIMARY KEY (ortID)
);

/* ---------------------------------------------------------------------- */
/* Add table "oc_termin"                                                  */
/* ---------------------------------------------------------------------- */

CREATE TABLE oc_termin (
    terminID NUMBER(8) NOT NULL,
    patientID NUMBER(8) NOT NULL,
    arztID NUMBER(8) NOT NULL,
    terminartID NUMBER(8) NOT NULL,
    terminZeitpunkt DATE NOT NULL,
    terminDauer NUMBER(5) NOT NULL,
    terminMitVorbehandlung CHAR(1) DEFAULT '0' NOT NULL,
    terminWahrgenommen CHAR(1) DEFAULT '0' NOT NULL,
    terminText CLOB,
    CONSTRAINT PK_oc_termin PRIMARY KEY (terminID)
);

CREATE INDEX IDX_oc_termin_1 ON oc_termin (patientID);

CREATE INDEX IDX_oc_termin_2 ON oc_termin (arztID);

CREATE INDEX IDX_oc_termin_3 ON oc_termin (terminartID);

ALTER TABLE oc_termin ADD CONSTRAINT CC_oc_termin_terminDauer 
    CHECK (terminDauer > 0);

/* ---------------------------------------------------------------------- */
/* Add table "oc_benutzer"                                                */
/* ---------------------------------------------------------------------- */

CREATE TABLE oc_benutzer (
    benutzerID NUMBER(8) NOT NULL,
    gruppeID NUMBER(8) NOT NULL,
    benutzerUsername VARCHAR2(255) DEFAULT '0' NOT NULL,
    benutzerPasswort VARCHAR2(255) NOT NULL,
    benutzerVorname VARCHAR2(255) DEFAULT '0',
    benutzerNachname VARCHAR2(255) DEFAULT '0',
    benutzerStatus CHAR(1) DEFAULT '0' NOT NULL,
    benutzerTyp CHAR(1),
    CONSTRAINT PK_oc_benutzer PRIMARY KEY (benutzerID)
);

CREATE INDEX IDX_oc_benutzer_1 ON oc_benutzer (gruppeID);

/* ---------------------------------------------------------------------- */
/* Add table "oc_gruppe"                                                  */
/* ---------------------------------------------------------------------- */

CREATE TABLE oc_gruppe (
    gruppeID NUMBER(8) NOT NULL,
    gruppeTyp CHAR(1) DEFAULT '0' NOT NULL,
    gruppeName VARCHAR2(255) DEFAULT '0' NOT NULL,
    CONSTRAINT PK_oc_gruppe PRIMARY KEY (gruppeID)
);

/* ---------------------------------------------------------------------- */
/* Add table "oc_recht"                                                   */
/* ---------------------------------------------------------------------- */

CREATE TABLE oc_recht (
    rechtID NUMBER(8) NOT NULL,
    rechtName VARCHAR2(1000) DEFAULT '0' NOT NULL,
    CONSTRAINT PK_oc_recht PRIMARY KEY (rechtID)
);

/* ---------------------------------------------------------------------- */
/* Add table "oc_gruppe_recht"                                            */
/* ---------------------------------------------------------------------- */

CREATE TABLE oc_gruppe_recht (
    gruppeID NUMBER(8) NOT NULL,
    rechtID NUMBER(8) NOT NULL,
    CONSTRAINT PK_oc_gruppe_recht PRIMARY KEY (gruppeID, rechtID)
);

CREATE INDEX IDX_oc_gruppe_recht_1 ON oc_gruppe_recht (rechtID);

CREATE INDEX IDX_oc_gruppe_recht_2 ON oc_gruppe_recht (gruppeID);

/* ---------------------------------------------------------------------- */
/* Add table "oc_fehlzeit"                                                */
/* ---------------------------------------------------------------------- */

CREATE TABLE oc_fehlzeit (
    fehlzeitID NUMBER(8) NOT NULL,
    benutzerID NUMBER(8) NOT NULL,
    fehlzeitVon DATE NOT NULL,
    fehlzeitBis DATE,
    fehlzeitGrund CLOB,
    CONSTRAINT PK_oc_fehlzeit PRIMARY KEY (fehlzeitID)
);

CREATE INDEX IDX_oc_fehlzeit_1 ON oc_fehlzeit (benutzerID);

/* ---------------------------------------------------------------------- */
/* Add table "oc_ordination"                                              */
/* ---------------------------------------------------------------------- */

CREATE TABLE oc_ordination (
    ordinationID NUMBER(8) NOT NULL,
    arztID NUMBER(8) NOT NULL,
    ordinationWochentag CHAR(2) NOT NULL,
    ordinationAnfangszeit DATE NOT NULL,
    ordinationEndzeit DATE NOT NULL,
    CONSTRAINT PK_oc_ordination PRIMARY KEY (ordinationID)
);

CREATE INDEX IDX_oc_ordination_1 ON oc_ordination (arztID);

/* ---------------------------------------------------------------------- */
/* Add table "oc_warteposition"                                           */
/* ---------------------------------------------------------------------- */

CREATE TABLE oc_warteposition (
    wartepositionID NUMBER(8) NOT NULL,
    terminID NUMBER(8),
    patientID NUMBER(8) NOT NULL,
    arztID NUMBER(8) NOT NULL,
    wartepositionNr NUMBER(3) DEFAULT 0 NOT NULL,
    CONSTRAINT PK_oc_warteposition PRIMARY KEY (wartepositionID),
    CONSTRAINT TUC_oc_warteposition_1 UNIQUE (patientID)
);

CREATE INDEX IDX_oc_warteposition_1 ON oc_warteposition (patientID);

CREATE INDEX IDX_oc_warteposition_2 ON oc_warteposition (arztID);

CREATE INDEX IDX_oc_warteposition_3 ON oc_warteposition (terminID);

/* ---------------------------------------------------------------------- */
/* Add table "oc_anamnese"                                                */
/* ---------------------------------------------------------------------- */

CREATE TABLE oc_anamnese (
    anamneseID NUMBER(8) NOT NULL,
    patientID NUMBER(8) NOT NULL,
    geandertVonID NUMBER(8) NOT NULL,
    geaendertAm DATE NOT NULL,
    anamneseTyp CHAR(1) DEFAULT '0' NOT NULL,
    anamneseText CLOB,
    anamneseStatus CHAR(1) DEFAULT '0' NOT NULL,
    CONSTRAINT PK_oc_anamnese PRIMARY KEY (anamneseID)
);

CREATE INDEX IDX_oc_anamnese_1 ON oc_anamnese (patientID);

/* ---------------------------------------------------------------------- */
/* Add table "oc_behandlung"                                              */
/* ---------------------------------------------------------------------- */

CREATE TABLE oc_behandlung (
    behandlungID NUMBER(8) NOT NULL,
    patientID NUMBER(8) NOT NULL,
    arztID NUMBER(8) NOT NULL,
    behandlungVon DATE,
    behandlungBis DATE,
    behandlungDiagnose CLOB,
    behandlungMitUeberweisung CHAR(1) DEFAULT '0',
    behandlungMitVerSchein CHAR(1) DEFAULT '0',
    behandlungMitTerminVermerk CHAR(1) DEFAULT '0',
    behandlungStatus CHAR(1) DEFAULT '0' NOT NULL,
    CONSTRAINT PK_oc_behandlung PRIMARY KEY (behandlungID)
);

CREATE INDEX IDX_oc_behandlung_1 ON oc_behandlung (patientID);

CREATE INDEX IDX_oc_behandlung_2 ON oc_behandlung (arztID);

/* ---------------------------------------------------------------------- */
/* Add table "oc_position"                                                */
/* ---------------------------------------------------------------------- */

CREATE TABLE oc_position (
    positionID NUMBER(8) NOT NULL,
    behandlungID NUMBER(8) NOT NULL,
    leistungID NUMBER(8) NOT NULL,
    positionText CLOB,
    positionSehtestL NUMBER(8,2),
    positionSehtestR NUMBER(8,2),
    positionFehlL NUMBER(8,2),
    positionFehlR NUMBER(8,2),
    positionBestimmtSchatten CHAR(1) DEFAULT '0' NOT NULL,
    positionBestimmtFraktrometer CHAR(1) DEFAULT '0' NOT NULL,
    positionBestimmtPhoropter CHAR(1) DEFAULT '0' NOT NULL,
    positionTyp CHAR(1),
    positionDatum DATE NOT NULL,
    CONSTRAINT PK_oc_position PRIMARY KEY (positionID)
);

CREATE INDEX IDX_oc_position_1 ON oc_position (behandlungID);

CREATE INDEX IDX_oc_position_2 ON oc_position (leistungID);

/* ---------------------------------------------------------------------- */
/* Add table "oc_leistung"                                                */
/* ---------------------------------------------------------------------- */

CREATE TABLE oc_leistung (
    leistungID NUMBER(8) NOT NULL,
    leistungCode VARCHAR(5) NOT NULL,
    leistungBezeichnung VARCHAR2(1000) DEFAULT '0' NOT NULL,
    CONSTRAINT PK_oc_leistung PRIMARY KEY (leistungID)
);

/* ---------------------------------------------------------------------- */
/* Add table "oc_hauptvers"                                               */
/* ---------------------------------------------------------------------- */

CREATE TABLE oc_hauptvers (
    hauptversID NUMBER(8) NOT NULL,
    patientID NUMBER(8) NOT NULL,
    hauptversVorname VARCHAR2(255) DEFAULT '0' NOT NULL,
    hauptversNachname VARCHAR2(255) DEFAULT '0' NOT NULL,
    hauptversGeschlecht CHAR(1) DEFAULT '0' NOT NULL,
    hauptversAnrede VARCHAR2(1000),
    hauptversTel VARCHAR2(30),
    hauptversSVN VARCHAR2(40),
    CONSTRAINT PK_oc_hauptvers PRIMARY KEY (hauptversID)
);

CREATE INDEX IDX_oc_hauptvers_1 ON oc_hauptvers (patientID);

/* ---------------------------------------------------------------------- */
/* Add table "oc_medikament"                                              */
/* ---------------------------------------------------------------------- */

CREATE TABLE oc_medikament (
    medikamentID NUMBER(8) NOT NULL,
    medikamentName VARCHAR2(255) NOT NULL,
    medikamentBeschreibung CLOB,
    medikamentPackungsgroesse VARCHAR2(1000) DEFAULT '0',
    medikamentStandardDosierung VARCHAR2(1000) DEFAULT '0',
    CONSTRAINT PK_oc_medikament PRIMARY KEY (medikamentID)
);

/* ---------------------------------------------------------------------- */
/* Add table "oc_terminart"                                               */
/* ---------------------------------------------------------------------- */

CREATE TABLE oc_terminart (
    terminartID NUMBER(8) NOT NULL,
    terminartBezeichnung VARCHAR2(1000),
    terminartDauer NUMBER(5) NOT NULL,
    terminartMitVorbehandlung CHAR(1) DEFAULT '0' NOT NULL,
    terminartIstStandard CHAR(1) DEFAULT '0' NOT NULL,
    CONSTRAINT PK_oc_terminart PRIMARY KEY (terminartID)
);

/* ---------------------------------------------------------------------- */
/* Add table "oc_mediver"                                                 */
/* ---------------------------------------------------------------------- */

CREATE TABLE oc_mediver (
    mediverID NUMBER(8) NOT NULL,
    medikamentID NUMBER(8) NOT NULL,
    patientID NUMBER(8),
    mediverDosierung VARCHAR2(1000),
    mediverVon DATE NOT NULL,
    mediverBis DATE,
    mediverMenge NUMBER(3) DEFAULT 0 NOT NULL,
    CONSTRAINT PK_oc_mediver PRIMARY KEY (mediverID)
);

CREATE INDEX IDX_oc_mediver_1 ON oc_mediver (medikamentID);

CREATE INDEX IDX_oc_mediver_2 ON oc_mediver (patientID);

/* ---------------------------------------------------------------------- */
/* Add table "oc_rezept"                                                  */
/* ---------------------------------------------------------------------- */

CREATE TABLE oc_rezept (
    rezeptID NUMBER(8) NOT NULL,
    arztID NUMBER(8) NOT NULL,
    patientID NUMBER(8) NOT NULL,
    behandlungID NUMBER(8) NOT NULL,
    rezeptDatum DATE NOT NULL,
    rezeptText CLOB,
    rezeptBezeichnung VARCHAR2(1000),
    rezeptLetzterDruck DATE,
    CONSTRAINT PK_oc_rezept PRIMARY KEY (rezeptID)
);

CREATE INDEX IDX_oc_rezept_1 ON oc_rezept (arztID);

CREATE INDEX IDX_oc_rezept_2 ON oc_rezept (patientID);

CREATE INDEX IDX_oc_rezept_3 ON oc_rezept (behandlungID);

/* ---------------------------------------------------------------------- */
/* Add table "oc_rechnung"                                                */
/* ---------------------------------------------------------------------- */

CREATE TABLE oc_rechnung (
    rechnungID NUMBER(8) NOT NULL,
    patientID NUMBER(8) NOT NULL,
    rechnungDatum DATE NOT NULL,
    rechnungBezahlt CHAR(1) DEFAULT '0' NOT NULL,
    CONSTRAINT PK_oc_rechnung PRIMARY KEY (rechnungID)
);

CREATE INDEX IDX_oc_rechnung_1 ON oc_rechnung (patientID);

/* ---------------------------------------------------------------------- */
/* Add table "oc_ueberweisung"                                            */
/* ---------------------------------------------------------------------- */

CREATE TABLE oc_ueberweisung (
    ueberweisungID NUMBER(8) NOT NULL,
    patientID NUMBER(8) NOT NULL,
    behandlungID NUMBER(8) NOT NULL,
    ueberweisungDatum DATE NOT NULL,
    ueberweisungFachsparte VARCHAR2(1000) DEFAULT '0' NOT NULL,
    ueberweisungGrund CLOB NOT NULL,
    CONSTRAINT PK_oc_ueberweisung PRIMARY KEY (ueberweisungID)
);

CREATE INDEX IDX_oc_ueberweisung_1 ON oc_ueberweisung (patientID);

CREATE INDEX IDX_oc_ueberweisung_2 ON oc_ueberweisung (behandlungID);

/* ---------------------------------------------------------------------- */
/* Add table "oc_brillenver"                                              */
/* ---------------------------------------------------------------------- */

CREATE TABLE oc_brillenver (
    brillenverID NUMBER(8) NOT NULL,
    patientID NUMBER(8) NOT NULL,
    behandlungID NUMBER(8) NOT NULL,
    brillenverDatum DATE NOT NULL,
    brillenverDioptrienL VARCHAR2(40) NOT NULL,
    brillenverDioptrienR VARCHAR2(40) NOT NULL,
    brillenverText CLOB,
    CONSTRAINT PK_oc_brillenver PRIMARY KEY (brillenverID)
);

CREATE INDEX IDX_oc_brillenver_1 ON oc_brillenver (patientID);

CREATE INDEX IDX_oc_brillenver_2 ON oc_brillenver (behandlungID);

/* ---------------------------------------------------------------------- */
/* Add table "oc_zeitbestaetigung"                                        */
/* ---------------------------------------------------------------------- */

CREATE TABLE oc_zeitbestaetigung (
    zeitbestaetigungID NUMBER(8) NOT NULL,
    patientID NUMBER(8) NOT NULL,
    zeitbestaetigungDatum DATE NOT NULL,
    zeitbestaetigungVon DATE NOT NULL,
    zeitbestaetigungBis DATE NOT NULL,
    zeitbestaetigungText CLOB,
    CONSTRAINT PK_oc_zeitbestaetigung PRIMARY KEY (zeitbestaetigungID)
);

CREATE INDEX IDX_oc_zeitbestaetigung_1 ON oc_zeitbestaetigung (patientID);

/* ---------------------------------------------------------------------- */
/* Add table "oc_vertretung"                                              */
/* ---------------------------------------------------------------------- */

CREATE TABLE oc_vertretung (
    vertretungID NUMBER(8) NOT NULL,
    patientID NUMBER(8) NOT NULL,
    vertretungDatum DATE NOT NULL,
    vertretungText CLOB,
    CONSTRAINT PK_oc_vertretung PRIMARY KEY (vertretungID)
);

CREATE INDEX IDX_oc_vertretung_1 ON oc_vertretung (patientID);

/* ---------------------------------------------------------------------- */
/* Add table "oc_land"                                                    */
/* ---------------------------------------------------------------------- */

CREATE TABLE oc_land (
    landID NUMBER(8) NOT NULL,
    landName VARCHAR2(1000) NOT NULL,
    landKurzform VARCHAR2(10) NOT NULL,
    CONSTRAINT PK_oc_land PRIMARY KEY (landID)
);

/* ---------------------------------------------------------------------- */
/* Add table "oc_institut"                                                */
/* ---------------------------------------------------------------------- */

CREATE TABLE oc_institut (
    institutID NUMBER(8) NOT NULL,
    institutName VARCHAR2(1000) NOT NULL,
    CONSTRAINT PK_oc_institut PRIMARY KEY (institutID)
);

/* ---------------------------------------------------------------------- */
/* Add table "oc_inst_patient"                                            */
/* ---------------------------------------------------------------------- */

CREATE TABLE oc_inst_patient (
    institutID NUMBER(8) NOT NULL,
    patientID NUMBER(8) NOT NULL,
    CONSTRAINT PK_oc_inst_patient PRIMARY KEY (institutID, patientID)
);

/* ---------------------------------------------------------------------- */
/* Add table "oc_patientlogin"                                            */
/* ---------------------------------------------------------------------- */

CREATE TABLE oc_patientlogin (
    patientID NUMBER(8) NOT NULL,
    terminID NUMBER(8),
    plUsername VARCHAR2(255) NOT NULL,
    plPasswort VARCHAR2(255) NOT NULL,
    CONSTRAINT PK_oc_patientlogin PRIMARY KEY (patientID)
);

/* ---------------------------------------------------------------------- */
/* Foreign key constraints                                                */
/* ---------------------------------------------------------------------- */

ALTER TABLE oc_patient ADD CONSTRAINT oc_ort_oc_patient 
    FOREIGN KEY (ortID) REFERENCES oc_ort (ortID);

ALTER TABLE oc_patient ADD CONSTRAINT oc_benutzer_oc_patient 
    FOREIGN KEY (arztID) REFERENCES oc_benutzer (benutzerID);

ALTER TABLE oc_patient ADD CONSTRAINT oc_benutzer_oc_patient_2 
    FOREIGN KEY (geandertVonID) REFERENCES oc_benutzer (benutzerID);

ALTER TABLE oc_ort ADD CONSTRAINT oc_land_oc_ort 
    FOREIGN KEY (landID) REFERENCES oc_land (landID);

ALTER TABLE oc_termin ADD CONSTRAINT oc_patient_oc_termin 
    FOREIGN KEY (patientID) REFERENCES oc_patient (patientID);

ALTER TABLE oc_termin ADD CONSTRAINT oc_benutzer_oc_termin 
    FOREIGN KEY (arztID) REFERENCES oc_benutzer (benutzerID);

ALTER TABLE oc_termin ADD CONSTRAINT oc_terminart_oc_termin 
    FOREIGN KEY (terminartID) REFERENCES oc_terminart (terminartID);

ALTER TABLE oc_benutzer ADD CONSTRAINT oc_gruppe_oc_benutzer 
    FOREIGN KEY (gruppeID) REFERENCES oc_gruppe (gruppeID);

ALTER TABLE oc_gruppe_recht ADD CONSTRAINT oc_recht_oc_gruppe_recht 
    FOREIGN KEY (rechtID) REFERENCES oc_recht (rechtID);

ALTER TABLE oc_gruppe_recht ADD CONSTRAINT oc_gruppe_oc_gruppe_recht 
    FOREIGN KEY (gruppeID) REFERENCES oc_gruppe (gruppeID);

ALTER TABLE oc_fehlzeit ADD CONSTRAINT oc_benutzer_oc_fehlzeit 
    FOREIGN KEY (benutzerID) REFERENCES oc_benutzer (benutzerID);

ALTER TABLE oc_ordination ADD CONSTRAINT oc_benutzer_oc_ordination 
    FOREIGN KEY (arztID) REFERENCES oc_benutzer (benutzerID);

ALTER TABLE oc_warteposition ADD CONSTRAINT oc_patient_oc_warteposition 
    FOREIGN KEY (patientID) REFERENCES oc_patient (patientID);

ALTER TABLE oc_warteposition ADD CONSTRAINT oc_benutzer_oc_warteposition 
    FOREIGN KEY (arztID) REFERENCES oc_benutzer (benutzerID);

ALTER TABLE oc_warteposition ADD CONSTRAINT oc_termin_oc_warteposition 
    FOREIGN KEY (terminID) REFERENCES oc_termin (terminID);

ALTER TABLE oc_anamnese ADD CONSTRAINT oc_patient_oc_anamnese 
    FOREIGN KEY (patientID) REFERENCES oc_patient (patientID);

ALTER TABLE oc_anamnese ADD CONSTRAINT oc_benutzer_oc_anamnese 
    FOREIGN KEY (geandertVonID) REFERENCES oc_benutzer (benutzerID);

ALTER TABLE oc_behandlung ADD CONSTRAINT oc_patient_oc_behandlung 
    FOREIGN KEY (patientID) REFERENCES oc_patient (patientID);

ALTER TABLE oc_behandlung ADD CONSTRAINT oc_benutzer_oc_behandlung 
    FOREIGN KEY (arztID) REFERENCES oc_benutzer (benutzerID);

ALTER TABLE oc_position ADD CONSTRAINT oc_behandlung_oc_position 
    FOREIGN KEY (behandlungID) REFERENCES oc_behandlung (behandlungID);

ALTER TABLE oc_position ADD CONSTRAINT oc_leistung_oc_position 
    FOREIGN KEY (leistungID) REFERENCES oc_leistung (leistungID);

ALTER TABLE oc_hauptvers ADD CONSTRAINT oc_patient_oc_hauptvers 
    FOREIGN KEY (patientID) REFERENCES oc_patient (patientID);

ALTER TABLE oc_mediver ADD CONSTRAINT oc_medikament_oc_mediver 
    FOREIGN KEY (medikamentID) REFERENCES oc_medikament (medikamentID);

ALTER TABLE oc_mediver ADD CONSTRAINT oc_patient_oc_mediver 
    FOREIGN KEY (patientID) REFERENCES oc_patient (patientID);

ALTER TABLE oc_rezept ADD CONSTRAINT oc_benutzer_oc_rezept 
    FOREIGN KEY (arztID) REFERENCES oc_benutzer (benutzerID);

ALTER TABLE oc_rezept ADD CONSTRAINT oc_patient_oc_rezept 
    FOREIGN KEY (patientID) REFERENCES oc_patient (patientID);

ALTER TABLE oc_rezept ADD CONSTRAINT oc_behandlung_oc_rezept 
    FOREIGN KEY (behandlungID) REFERENCES oc_behandlung (behandlungID);

ALTER TABLE oc_rechnung ADD CONSTRAINT oc_patient_oc_rechnung 
    FOREIGN KEY (patientID) REFERENCES oc_patient (patientID);

ALTER TABLE oc_ueberweisung ADD CONSTRAINT oc_patient_oc_ueberweisung 
    FOREIGN KEY (patientID) REFERENCES oc_patient (patientID);

ALTER TABLE oc_ueberweisung ADD CONSTRAINT oc_behandlung_oc_ueberweisung 
    FOREIGN KEY (behandlungID) REFERENCES oc_behandlung (behandlungID);

ALTER TABLE oc_brillenver ADD CONSTRAINT oc_patient_oc_brillenver 
    FOREIGN KEY (patientID) REFERENCES oc_patient (patientID);

ALTER TABLE oc_brillenver ADD CONSTRAINT oc_behandlung_oc_brillenver 
    FOREIGN KEY (behandlungID) REFERENCES oc_behandlung (behandlungID);

ALTER TABLE oc_zeitbestaetigung ADD CONSTRAINT oc_patient_oc_zeitbestaetigung 
    FOREIGN KEY (patientID) REFERENCES oc_patient (patientID);

ALTER TABLE oc_vertretung ADD CONSTRAINT oc_patient_oc_vertretung 
    FOREIGN KEY (patientID) REFERENCES oc_patient (patientID);

ALTER TABLE oc_inst_patient ADD CONSTRAINT oc_institut_oc_inst_patient 
    FOREIGN KEY (institutID) REFERENCES oc_institut (institutID);

ALTER TABLE oc_inst_patient ADD CONSTRAINT oc_patient_oc_inst_patient 
    FOREIGN KEY (patientID) REFERENCES oc_patient (patientID);

ALTER TABLE oc_patientlogin ADD CONSTRAINT oc_patient_oc_patientlogin 
    FOREIGN KEY (patientID) REFERENCES oc_patient (patientID);

ALTER TABLE oc_patientlogin ADD CONSTRAINT oc_termin_oc_patientlogin 
    FOREIGN KEY (terminID) REFERENCES oc_termin (terminID);

/* ---------------------------------------------------------------------- */
/* Triggers                                                               */
/* ---------------------------------------------------------------------- */

CREATE OR REPLACE TRIGGER Trg_oc_patient1
BEFORE INSERT ON oc_patient FOR EACH ROW
BEGIN
  SELECT seq_patient.NEXTVAL
  INTO :NEW.patientID
  FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER Trg_oc_ort1
BEFORE INSERT ON oc_ort FOR EACH ROW
BEGIN
  SELECT seq_ort.NEXTVAL
  INTO :NEW.ortID
  FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER Trg_oc_termin1
BEFORE INSERT ON oc_termin FOR EACH ROW
BEGIN
  SELECT seq_termin.NEXTVAL
  INTO :NEW.terminID
  FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER Trg_oc_benutzer1
BEFORE INSERT ON oc_benutzer FOR EACH ROW
BEGIN
  SELECT seq_benutzer.NEXTVAL
  INTO :NEW.benutzerID
  FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER Trg_oc_gruppe1
BEFORE INSERT ON oc_gruppe FOR EACH ROW
BEGIN
  SELECT seq_gruppe.NEXTVAL
  INTO :NEW.gruppeID
  FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER Trg_oc_recht1
BEFORE INSERT ON oc_recht FOR EACH ROW
BEGIN
  SELECT seq_recht.NEXTVAL
  INTO :NEW.rechtID
  FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER Trg_oc_fehlzeit1
BEFORE INSERT ON oc_fehlzeit FOR EACH ROW
BEGIN
  SELECT seq_fehlzeit.NEXTVAL
  INTO :NEW.fehlzeitID
  FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER Trg_oc_ordination1
BEFORE INSERT ON oc_ordination FOR EACH ROW
BEGIN
  SELECT seq_ordination.NEXTVAL
  INTO :NEW.ordinationID
  FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER Trg_oc_warteposition1
BEFORE INSERT ON oc_warteposition FOR EACH ROW
BEGIN
  SELECT seq_warteposition.NEXTVAL
  INTO :NEW.wartepositionID
  FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER Trg_oc_anamnese1
BEFORE INSERT ON oc_anamnese FOR EACH ROW
BEGIN
  SELECT seq_anamnese.NEXTVAL
  INTO :NEW.anamneseID
  FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER Trg_oc_behandlung1
BEFORE INSERT ON oc_behandlung FOR EACH ROW
BEGIN
  SELECT seq_behandlung.NEXTVAL
  INTO :NEW.behandlungID
  FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER Trg_oc_position1
BEFORE INSERT ON oc_position FOR EACH ROW
BEGIN
  SELECT seq_position.NEXTVAL
  INTO :NEW.positionID
  FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER Trg_oc_leistung1
BEFORE INSERT ON oc_leistung FOR EACH ROW
BEGIN
  SELECT seq_leistung.NEXTVAL
  INTO :NEW.leistungID
  FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER Trg_oc_hauptvers1
BEFORE INSERT ON oc_hauptvers FOR EACH ROW
BEGIN
  SELECT seq_hauptvers.NEXTVAL
  INTO :NEW.hauptversID
  FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER Trg_oc_medikament1
BEFORE INSERT ON oc_medikament FOR EACH ROW
BEGIN
  SELECT seq_medikament.NEXTVAL
  INTO :NEW.medikamentID
  FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER Trg_oc_terminart1
BEFORE INSERT ON oc_terminart FOR EACH ROW
BEGIN
  SELECT seq_terminart.NEXTVAL
  INTO :NEW.terminartID
  FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER Trg_oc_mediver1
BEFORE INSERT ON oc_mediver FOR EACH ROW
BEGIN
  SELECT seq_mediver.NEXTVAL
  INTO :NEW.mediverID
  FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER Trg_oc_rezept1
BEFORE INSERT ON oc_rezept FOR EACH ROW
BEGIN
  SELECT seq_rezept.NEXTVAL
  INTO :NEW.rezeptID
  FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER Trg_oc_rechnung1
BEFORE INSERT ON oc_rechnung FOR EACH ROW
BEGIN
  SELECT seq_rechnung.NEXTVAL
  INTO :NEW.rechnungID
  FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER Trg_oc_ueberweisung1
BEFORE INSERT ON oc_ueberweisung FOR EACH ROW
BEGIN
  SELECT seq_ueberweisung.NEXTVAL
  INTO :NEW.ueberweisungID
  FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER Trg_oc_brillenver1
BEFORE INSERT ON oc_brillenver FOR EACH ROW
BEGIN
  SELECT seq_brillenver.NEXTVAL
  INTO :NEW.brillenverID
  FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER Trg_oc_zeitbestaetigung1
BEFORE INSERT ON oc_zeitbestaetigung FOR EACH ROW
BEGIN
  SELECT seq_zeitbestaetigung.NEXTVAL
  INTO :NEW.zeitbestaetigungID
  FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER Trg_oc_vertretung1
BEFORE INSERT ON oc_vertretung FOR EACH ROW
BEGIN
  SELECT seq_vertretung.NEXTVAL
  INTO :NEW.vertretungID
  FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER Trg_oc_land1
BEFORE INSERT ON oc_land FOR EACH ROW
BEGIN
  SELECT seq_land.NEXTVAL
  INTO :NEW.landID
  FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER Trg_oc_versicherung1
BEFORE INSERT ON oc_institut FOR EACH ROW
BEGIN
  SELECT seq_versicherung.NEXTVAL
  INTO :NEW.institutID
  FROM DUAL;
END;
/
